/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import models.entity.Color;
import models.entity.Gama;
import models.entity.Marca;
import models.entity.Modelo;
import models.entity.Vehiculo;
import utilidades.DataBaseInstance;

/**
 *
 * @author hmardones
 */
public class VehiculoDao {
     
     //***//
      public List<Vehiculo> getVehiculo(){
        List<Vehiculo> listaVehiculos = new LinkedList<Vehiculo>();
        ResultSet result = null;
     try {

            String query = "SELECT "
                            + " v.PATENTE,"
                            + " v.MODELO,"
                            + " v.KM,"
                            + " v.ANIO,"
                            + " v.GAMA,"
                            + " v.PRECIO,"
                            + " v.COLOR,"
                            + " v.TIPO_MOTOR,"
                            + " v.GASOLINA_VEHICULO,"
                            + " v.TRANSMISION,"
                            + " v.AIRBAG,"
                            + " v.SISTEMA_ABS,"
                            + " v.AIRE_ACONDICIONADO,"
                            + " v.NUMERO_CHASIS,"
                            + " mo.NOMBRE,"
                            + " mo.ID_MARCA,"
                            + " mo.ID_MODELO,"
                            + " g.ID_GAMA,"
                            + " ma.DESCRIPCION as D1,"                           
                            + " g.ID_GAMA,"
                            + " g.DESCRIPCION as D2,"
                            + " c.DESCRIPCION as D3 "
                        + " FROM "
                            + " VEHICULOS v,"
                            + " MODELO mo, "
                            + " MARCA ma,"
                            + " GAMA g,"
                            + " COLOR c"
                        + " WHERE "
                            + " mo.ID_MARCA = ma.ID_MARCA "
                            + " and v.MODELO = mo.ID_MODELO "
                            + " and v.GAMA=g.ID_GAMA "
                            + " and c.ID_COLOR=v.COLOR ";
            Connection conn =  DataBaseInstance.getInstanceConnection();
            Statement stmt = conn.createStatement();
            result = stmt.executeQuery(query);
            Vehiculo vehiculo;
            Modelo modelo;
            Marca marca;
            Gama gama;
            Color color;
            while (result.next()) {
                modelo=new Modelo();
                marca=new Marca();
                gama=new Gama();
                color=new Color();
                vehiculo=new Vehiculo();
                vehiculo.setPatente(result.getString("PATENTE"));
                vehiculo.setKm(result.getInt("KM"));
                vehiculo.setAnio(result.getInt("ANIO"));
                vehiculo.setPrecio(result.getInt("PRECIO"));
                vehiculo.setTipo_motor(result.getString("TIPO_MOTOR"));
                vehiculo.setGasolina_vehiculo(result.getString("GASOLINA_VEHICULO"));
                vehiculo.setTransmision(result.getString("TRANSMISION"));
                vehiculo.setSistema_abs(result.getString("SISTEMA_ABS"));
                vehiculo.setAire_acondicionado(result.getString("AIRE_ACONDICIONADO"));
                vehiculo.setAirbag(result.getString("AIRBANG"));
                vehiculo.setNumero_chasis(result.getInt("NUMERO_CHASIS"));
                gama.setId_gama(result.getInt("ID_GAMA"));
                gama.setDescripcion(result.getString("D2"));
                color.setId_color(result.getInt("ID_COLOR"));
                color.setDescripcion(result.getString("D3"));
                modelo.setNombre(result.getString("NOMBRE"));
                modelo.setId_modelo(result.getInt("ID_MODELO"));
                marca.setDescripcion(result.getString("D1"));
                marca.setId_marca(result.getInt("ID_MARCA"));
                modelo.setMarca(marca);
                vehiculo.setModelo(modelo);
                vehiculo.setGama(gama);
                vehiculo.setColor(color);
                listaVehiculos.add(vehiculo);
            }
            result.close();
            stmt.close();
            DataBaseInstance.closeConnection(conn);

        } catch (SQLException se) {
            System.out.println(se.toString());
            System.err.println("Se ha producido un error de BD.");
            System.err.println(se.getMessage());
        }
        return listaVehiculos;
    }
      public List<Vehiculo> findAll() {
        List<Vehiculo> listaVehiculos = new LinkedList<Vehiculo>();
        ResultSet result = null;

        try {
            
            String query = "select  "
                                + " v.PATENTE,"
                                + " v.MODELO,"
                                + " v.KM,"
                                + " v.ANIO,"
                                + " v.GAMA,"
                                + " v.PRECIO,"
                                + " v.COLOR,"
                                + " v.TIPO_MOTOR,"
                                + " v.GASOLINA_VEHICULO,"
                                + " v.TRANSMISION,"
                                + " v.AIRBAG,"
                                + " v.SISTEMA_ABS,"
                                + " v.AIRE_ACONDICIONADO,"
                                + " v.NUMERO_CHASIS,"
                                + " mo.NOMBRE,"
                                + " mo.ID_MARCA,"
                                + " mo.ID_MODELO,"
                                + " ma.DESCRIPCION as D1,"
                                + " g.DESCRIPCION as D2,"
                                + " c.DESCRIPCION as D3 "
                    + "from "
                                + " VEHICULOS v,"
                                + " MODELO mo,"
                                + " MARCA ma,"
                                + " GAMA g,"
                                + " COLOR c "
                    + "WHERE "
                                + " mo.ID_MARCA = ma.ID_MARCA "
                                + " and v.MODELO = mo.ID_MODELO "
                                + " and v.GAMA=g.ID_GAMA "
                                + " and c.ID_COLOR=v.COLOR;";
            Connection conn = DataBaseInstance.getInstanceConnection();
            Statement stmt = conn.createStatement();
            result = stmt.executeQuery(query);
            Vehiculo vehiculo;
            Modelo modelo;
            Marca marca;
            Gama gama;
            Color color;
            while (result.next()) {
                modelo=new Modelo();
                marca=new Marca();
                gama=new Gama();
                color=new Color();
                vehiculo=new Vehiculo();
                vehiculo.setPatente(result.getString("PATENTE"));
                vehiculo.setKm(result.getInt("KM"));
                vehiculo.setAnio(result.getInt("ANIO"));
                vehiculo.setPrecio(result.getInt("PRECIO"));
                vehiculo.setTipo_motor(result.getString("TIPO_MOTOR"));
                vehiculo.setGasolina_vehiculo(result.getString("GASOLINA_VEHICULO"));
                vehiculo.setTransmision(result.getString("TRANSMISION"));
                vehiculo.setSistema_abs(result.getString("SISTEMA_ABS"));
                vehiculo.setAire_acondicionado(result.getString("AIRE_ACONDICIONADO"));
                vehiculo.setAirbag(result.getString("AIRBAG"));
                vehiculo.setNumero_chasis(result.getInt("NUMERO_CHASIS"));
                gama.setId_gama(result.getInt("ID_GAMA"));
                gama.setDescripcion(result.getString("D2"));
                color.setId_color(result.getInt("ID_COLOR"));
                color.setDescripcion(result.getString("D3"));
                modelo.setNombre(result.getString("NOMBRE"));
                modelo.setId_modelo(result.getInt("ID_MODELO"));
                marca.setDescripcion(result.getString("D1"));
                marca.setId_marca(result.getInt("ID_MARCA"));
                modelo.setMarca(marca);
                vehiculo.setModelo(modelo);
                vehiculo.setGama(gama);
                vehiculo.setColor(color);
                listaVehiculos.add(vehiculo);
            }

            result.close();
            stmt.close();
          DataBaseInstance.closeConnection(conn);

        } catch (SQLException se) {
            System.out.println(se.toString());
            System.err.println("Se ha producido un error de BD.");
            System.err.println(se.getMessage());
        }

        return listaVehiculos;
    }
      //buscar por el id
    public Vehiculo findById(String id) {
        ResultSet result = null;
        Vehiculo vehiculo = null;
        try {

            String query = "SELECT "
                            + " v.PATENTE,"
                            + " v.MODELO,"
                            + " v.KM,"
                            + " v.ANIO,"
                            + " v.GAMA,"
                            + " v.PRECIO,"
                            + " v.COLOR,"
                            + " v.TIPO_MOTOR,"
                            + " v.GASOLINA_VEHICULO,"
                            + " v.TRANSMISION,"
                            + " v.AIRBAG,"
                            + " v.SISTEMA_ABS,"
                            + " v.AIRE_ACONDICIONADO,"
                            + " v.NUMERO_CHASIS,"
                            + " mo.NOMBRE,"
                            + " mo.ID_MARCA,"
                            + " mo.ID_MODELO,"
                            + " g.ID_GAMA,"
                            + " ma.DESCRIPCION as D1,"                           
                            + " g.ID_GAMA,"
                            + " g.DESCRIPCION as D2,"
                            + " c.DESCRIPCION as D3 "
                        + " FROM "
                            + " VEHICULOS v,"
                            + " MODELO mo, "
                            + " MARCA ma,"
                            + " GAMA g,"
                            + " COLOR c"
                        + " WHERE "
                            + " mo.ID_MARCA = ma.ID_MARCA "
                            + " and v.MODELO = mo.ID_MODELO "
                            + " and v.GAMA=g.ID_GAMA "
                            + " and c.ID_COLOR=v.COLOR "
                            + " AND"
                            + " v.PATENTE = ?";


            Connection conn = DataBaseInstance.getInstanceConnection();
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, id);
            result = stmt.executeQuery();

            Modelo modelo;
            Marca marca;
            Gama gama;
            Color color;
            if (result.next()) {
                modelo=new Modelo();
                marca=new Marca();
                gama=new Gama();
                color=new Color();
                vehiculo=new Vehiculo();
                vehiculo.setPatente(result.getString("PATENTE"));
                vehiculo.setKm(result.getInt("KM"));
                vehiculo.setAnio(result.getInt("ANIO"));
                vehiculo.setPrecio(result.getInt("PRECIO"));
                vehiculo.setTipo_motor(result.getString("TIPO_MOTOR"));
                vehiculo.setGasolina_vehiculo(result.getString("GASOLINA_VEHICULO"));
                vehiculo.setTransmision(result.getString("TRANSMISION"));
                vehiculo.setSistema_abs(result.getString("SISTEMA_ABS"));
                vehiculo.setAire_acondicionado(result.getString("AIRE_ACONDICIONADO"));
                vehiculo.setAirbag(result.getString("AIRBAG"));
                vehiculo.setNumero_chasis(result.getInt("NUMERO_CHASIS"));
                gama.setId_gama(result.getInt("ID_GAMA"));
                gama.setDescripcion(result.getString("D2"));
                color.setId_color(result.getInt("COLOR"));
                color.setDescripcion(result.getString("D3"));
                modelo.setNombre(result.getString("NOMBRE"));
                modelo.setId_modelo(result.getInt("ID_MODELO"));
                marca.setDescripcion(result.getString("D1"));
                marca.setId_marca(result.getInt("ID_MARCA"));
                modelo.setMarca(marca);
                vehiculo.setModelo(modelo);
                vehiculo.setGama(gama);
                vehiculo.setColor(color);
            }
            result.close();
            stmt.close();
            DataBaseInstance.closeConnection(conn);

            } catch (SQLException se) {
                System.out.println(se.toString());
                System.err.println("Se ha producido un error de BD.");
                System.err.println(se.getMessage());
            }

        return vehiculo;
    }
    //guardar
    public void Insertar(Vehiculo vehiculo) {
        Modelo mo=vehiculo.getModelo();
        Color c=vehiculo.getColor();
        Gama g=vehiculo.getGama();
        PreparedStatement saveVehiculo;
        try {
                Connection conn = DataBaseInstance.getInstanceConnection();
                saveVehiculo = conn.prepareStatement(
                        "INSERT INTO VEHICULOS (PATENTE,"
                                + " MODELO,"
                                + " KM,"
                                + " ANIO,"
                                + " GAMA,"
                                + " PRECIO, "
                                + " COLOR,"
                                + " TIPO_MOTOR,"
                                + " GASOLINA_VEHICULO,"
                                + " TRANSMISION,"
                                + " AIRBAG,"
                                + " SISTEMA_ABS,"
                                + " AIRE_ACONDICIONADO,"
                                + " NUMERO_CHASIS)"
                        + "VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?)");
                saveVehiculo.setString(1,vehiculo.getPatente());
                saveVehiculo.setString(2,mo.getNombre());
                saveVehiculo.setInt(3, vehiculo.getKm());
                saveVehiculo.setInt(4, vehiculo.getAnio());
                saveVehiculo.setString(5, g.getDescripcion());
                saveVehiculo.setInt(6, vehiculo.getPrecio());
                saveVehiculo.setString(7, c.getDescripcion());
                saveVehiculo.setString(8, vehiculo.getTipo_motor());
                saveVehiculo.setString(9, vehiculo.getGasolina_vehiculo());
                saveVehiculo.setString(10, vehiculo.getTransmision());
                saveVehiculo.setString(11, vehiculo.getAirbag());
                saveVehiculo.setString(12,vehiculo.getSistema_abs());
                saveVehiculo.setString(13, vehiculo.getAire_acondicionado());
                saveVehiculo.setInt(14,vehiculo.getNumero_chasis());
                System.out.println("INSERT INTO ....");            

            saveVehiculo.executeUpdate();
            DataBaseInstance.closeConnection(conn);
        } catch (SQLException se) {
            System.err.println("Se ha producido un error de BD.");
            System.err.println(se.getMessage());
        }
    }
    public void actualizar(Vehiculo vehiculo) {
        Modelo mo=vehiculo.getModelo();
        Color c=vehiculo.getColor();
        Gama g=vehiculo.getGama();
        PreparedStatement saveVehiculo;
        try {
                Connection conn = DataBaseInstance.getInstanceConnection();
                saveVehiculo = conn.prepareStatement(
                        "UPDATE VEHICULOS SET "
                                    + " MODELO = ?,"
                                    + " KM = ?,"
                                    + " ANIO = ?,"
                                    + " GAMA=?,"
                                    + " PRECIO=?,"
                                    + " COLOR=?,"
                                    + " TIPO_MOTOR=?,"
                                    + " GASOLINA_VEHICULO=?,"
                                    + " TRANSMISION=?,"
                                    + " AIRBANG=?,"
                                    + " SISTEMA_ABS=?,"
                                    + " ACONDICIONADO=?,"
                                    + " NUMERO_CHASIS=? "
                                + "WHERE  "
                                    + " PATENTE = ?");
                
                saveVehiculo.setString(1,mo.getNombre());
                saveVehiculo.setInt(2, vehiculo.getKm());
                saveVehiculo.setInt(3, vehiculo.getAnio());
                saveVehiculo.setString(4, g.getDescripcion());
                saveVehiculo.setInt(5, vehiculo.getPrecio());
                saveVehiculo.setString(6, c.getDescripcion());
                saveVehiculo.setString(7, vehiculo.getTipo_motor());
                saveVehiculo.setString(8, vehiculo.getGasolina_vehiculo());
                saveVehiculo.setString(9, vehiculo.getTransmision());
                saveVehiculo.setString(10, vehiculo.getAirbag());
                saveVehiculo.setString(11,vehiculo.getSistema_abs());
                saveVehiculo.setString(12, vehiculo.getAire_acondicionado());
                saveVehiculo.setInt(13,vehiculo.getNumero_chasis());
                saveVehiculo.setString(14,vehiculo.getPatente());
            

            saveVehiculo.executeUpdate();
            DataBaseInstance.closeConnection(conn);
        } catch (SQLException se) {
            System.err.println("Se ha producido un error de BD.");
            System.err.println(se.getMessage());
        }
    }
    public void delete(Vehiculo vehiculo) {
        PreparedStatement saveProduct;
        try {
            Connection conn = DataBaseInstance.getInstanceConnection();
                saveProduct = conn.prepareStatement(
                        "DELETE FROM VEHICULOS WHERE PATENTE = ?");

                saveProduct.setString(1, vehiculo.getPatente());
                saveProduct.executeUpdate(); 
                
          DataBaseInstance.closeConnection(conn);
        } catch (SQLException se) {
            System.err.println("Se ha producido un error de BD.");
            System.err.println(se.getMessage());
        }
    }
    public List<Vehiculo> findString(String hombre) {
        List<Vehiculo> listaVehiculos = new LinkedList<Vehiculo>();
        ResultSet result = null;
        try {

            String query = "SELECT "
                            + " v.PATENTE,"
                            + " v.MODELO,"
                            + " v.KM,"
                            + " v.ANIO,"
                            + " v.GAMA,"
                            + " v.PRECIO,"
                            + " v.COLOR,"
                            + " v.TIPO_MOTOR,"
                            + " v.GASOLINA_VEHICULO,"
                            + " v.TRANSMISION,"
                            + " v.AIRBAG,"
                            + " v.SISTEMA_ABS,"
                            + " v.AIRE_ACONDICIONADO,"
                            + " v.NUMERO_CHASIS,"
                            + " mo.NOMBRE,"
                            + " mo.ID_MARCA,"
                            + " mo.ID_MODELO,"
                            + " g.ID_GAMA,"
                            + " ma.DESCRIPCION as D1,"                           
                            + " g.ID_GAMA,"
                            + " g.DESCRIPCION as D2,"
                            + " c.DESCRIPCION as D3 "
                        + " FROM "
                            + " VEHICULOS v,"
                            + " MODELO mo, "
                            + " MARCA ma,"
                            + " GAMA g,"
                            + " COLOR c"
                        + " WHERE "
                            + " mo.ID_MARCA = ma.ID_MARCA "
                            + " and v.MODELO = mo.ID_MODELO "
                            + " and v.GAMA=g.ID_GAMA "
                            + " and c.ID_COLOR=v.COLOR "
                            + " AND"
                            + " ("
                            + " ma.DESCRIPCION LIKE ? "
                            + " OR "
                            + " mo.NOMBRE LIKE ? "
                            + " )";


            Connection conn = DataBaseInstance.getInstanceConnection();
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, "%" + hombre + "%");
            stmt.setString(2, "%" + hombre + "%");
            result = stmt.executeQuery();

            Vehiculo vehiculo;
            Modelo modelo;
            Marca marca;
            Gama gama;
            Color color;
            while (result.next()) {
                modelo=new Modelo();
                marca=new Marca();
                gama=new Gama();
                color=new Color();
                vehiculo=new Vehiculo();
                vehiculo.setPatente(result.getString("PATENTE"));
                vehiculo.setKm(result.getInt("KM"));
                vehiculo.setAnio(result.getInt("ANIO"));
                vehiculo.setPrecio(result.getInt("PRECIO"));
                vehiculo.setTipo_motor(result.getString("TIPO_MOTOR"));
                vehiculo.setGasolina_vehiculo(result.getString("GASOLINA_VEHICULO"));
                vehiculo.setTransmision(result.getString("TRANSMISION"));
                vehiculo.setSistema_abs(result.getString("SISTEMA_ABS"));
                vehiculo.setAire_acondicionado(result.getString("AIRE_ACONDICIONADO"));
                vehiculo.setAirbag(result.getString("AIRBAG"));
                vehiculo.setNumero_chasis(result.getInt("NUMERO_CHASIS"));
                gama.setId_gama(result.getInt("ID_GAMA"));
                gama.setDescripcion(result.getString("D2"));
                color.setId_color(result.getInt("COLOR"));
                color.setDescripcion(result.getString("D3"));
                modelo.setNombre(result.getString("NOMBRE"));
                modelo.setId_modelo(result.getInt("ID_MODELO"));
                marca.setDescripcion(result.getString("D1"));
                marca.setId_marca(result.getInt("ID_MARCA"));
                modelo.setMarca(marca);
                vehiculo.setModelo(modelo);
                vehiculo.setGama(gama);
                vehiculo.setColor(color);
                listaVehiculos.add(vehiculo);
            }
            result.close();
            stmt.close();
            DataBaseInstance.closeConnection(conn);

            } catch (SQLException se) {
                System.out.println(se.toString());
                System.err.println("Se ha producido un error de BD.");
                System.err.println(se.getMessage());
            }
        return listaVehiculos;
    }
}
